package com.dao.impl;

import com.dao.PageDao;
import com.entity.*;
import com.util.DBUtil;
import org.jasypt.util.password.PasswordEncryptor;
import org.jasypt.util.password.StrongPasswordEncryptor;
import org.jasypt.util.text.StrongTextEncryptor;

import java.sql.*;
import java.time.Instant;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Objects;

public class PageDaoImpl implements PageDao {
    private static final String SELECT_SEARCHADMIN_SQL = "select userid,username,pwdHash,pwhHashCode,birthday,sex,usertype,isfull,money from users where 1=1";
    private static final String SELECT_SEARCHBOOK_SQL = "select bookid,bookname,publish,author,bookcount,booktype from book where 1=1";
    private static final String SELECT_SEARCHBORROW_SQL = "select operatid,bookid,userid,bookname,username,borrowtime,renttime,isreturn from operatebook where 1=1";
    private static final String SELECT_COUNTADMIN_SQL = "select count(*) from users where 1=1";
    private static final String SELECT_COUNTBOOK_SQL = "select count(*) from book where 1=1";
    private static final String SELECT_COUNTBORROW_SQL = "select count(*) from operatebook where 1=1";
    private PasswordEncryptor passwordEncryptor;


    public PageDaoImpl() {
        this.passwordEncryptor = new StrongPasswordEncryptor();
    }

    @Override
    public Page getPageBySearch(String name, UserCriteria userCriteria) throws Exception {
        String limitName = null;
        String limitOther =null;
        String selectSql =null;
        String CountSql =null;
        int type = 0;
        List<Object> data = new ArrayList<>();
        Page page = new Page<>();
        if (name.equals("Users")) {
            limitName = "username like ?";
            limitOther ="usertype like ?";
            selectSql=SELECT_SEARCHADMIN_SQL;
            CountSql =SELECT_COUNTADMIN_SQL;
            userCriteria.setSortBy("userid");
        }else if(name.equals("book")){
            limitName = "bookname like ?";
            limitOther ="author like ?";
            selectSql= SELECT_SEARCHBOOK_SQL;
            CountSql = SELECT_COUNTBOOK_SQL;
            userCriteria.setSortBy("bookid");
        }else {
            limitName = "username like ?";
            limitOther ="bookname like ?";
            selectSql= SELECT_SEARCHBORROW_SQL;
            CountSql = SELECT_COUNTBORROW_SQL;
            userCriteria.setSortBy("operatid");
        }
        int pageNumber = userCriteria.getPageNumber();
        int pageSize = userCriteria.getPageSize();

        List<String> segments = new ArrayList<>();
        List<Object> params = new ArrayList<>();
        if(Objects.nonNull(userCriteria.getName())&& !userCriteria.getName().isEmpty()){
            segments.add(limitName);
            params.add("%"+userCriteria.getName()+"%");
        }
        if(Objects.nonNull(userCriteria.getUsertypeOrAuthor())&& !userCriteria.getUsertypeOrAuthor().isEmpty()){
            segments.add(limitOther);
            if(userCriteria.getUsertypeOrAuthor().equals("读者")){
                type=1;
            }else if(userCriteria.getUsertypeOrAuthor().equals("图书管理员")){
                type = 2;
            }else {
                type = 3;
            }
            params.add(name=="Users"?type:userCriteria.getUsertypeOrAuthor());
        }
        if(segments.size()>0){
            selectSql+=" AND %s";
            CountSql+=" AND %s";
        }

        StringBuffer sql =new StringBuffer(String.format(selectSql,String.join(" OR ", segments)));
        String sqlCount = String.format(CountSql,String.join(" OR ", segments));

        if(Objects.nonNull(userCriteria.getSortBy())&&!userCriteria.getSortBy().isEmpty()){
            sql.append(" ORDER BY ").append(userCriteria.getSortBy());
            if(Objects.nonNull(userCriteria.getSortDirection())&&!userCriteria.getSortDirection().isEmpty()){
                sql.append(" ").append(userCriteria.getSortDirection());
            }
        }
        sql.append(" LIMIT ").append((userCriteria.getPageNumber()<=0?0:userCriteria.getPageNumber()-1)*userCriteria.getPageSize()).append(",").append(userCriteria.getPageSize());

        try (Connection connection = DBUtil.getConnection();
             PreparedStatement ps = connection.prepareStatement(sql.toString());
             PreparedStatement psCount = connection.prepareStatement(sqlCount)
        ) {
            for (int i = 0;i<params.size();i++){
                if(params.get(i) instanceof Integer){
                    ps.setInt(i+1, (Integer) params.get(i));
                    psCount.setInt(i+1, (Integer) params.get(i));
                }else {
                    ps.setString(i+1, (String) params.get(i));
                    psCount.setString(i+1,(String) params.get(i));
                }
            }
            ResultSet rs = psCount.executeQuery();
            int pageCount =0;
            if(rs.next()){
                pageCount=rs.getInt(1);
            }
            page.setTotalCount(pageCount);
            page.setPageNumber(pageNumber);
            page.setPageSize(pageSize);
            page.setTotalPage((pageCount / pageSize * pageSize >= pageCount) ? pageCount / pageSize : (pageCount / pageSize + 1));

            ResultSet rs1 = ps.executeQuery();
            while (rs1.next()){
                if(name.equals("Users")){
                    Users users = getUsers(rs1);
                    data.add(users);
                }else if(name.equals("book")){
                    Book books = getBooks(rs1);
                    data.add(books);
                }else {
                    Operatebook operatebook = getOperatebook(rs1);
                    data.add(operatebook);
                }
            }
            page.setData(data);
        }
        return page;
    }

    private static Operatebook getOperatebook(ResultSet rs1) throws SQLException {
        Operatebook operatebook = new Operatebook();
        operatebook.setOperatid(rs1.getLong("operatid"));
        operatebook.setBookid(rs1.getLong("bookid"));
        operatebook.setUserid(rs1.getLong("userid"));
        operatebook.setBookname(rs1.getString("bookname"));
        operatebook.setUsername(rs1.getString("username"));
//        operatebook.setBorrowtime(localDateTime);
//        operatebook.setRenttime(rs1.getDate("renttime"));
        operatebook.setBorrowtime(null);
        operatebook.setRenttime(null);
        operatebook.setIsreturn(rs1.getInt("isreturn"));
        return operatebook;
    }

    private Book getBooks(ResultSet rs) throws Exception {
        Book book = new Book();
        book.setBookid(rs.getLong("bookid"));
        book.setBookname(rs.getString("bookname"));
        book.setPublish(rs.getString("publish"));
        book.setAuthor(rs.getString("author"));
        book.setBookcount(rs.getLong("bookcount"));
        book.setBooktype(rs.getString("booktype"));
        return book;
    }

    private static Users getUsers(ResultSet rs) throws SQLException {
        Users user = new Users();
        user.setUserid(rs.getLong("userid"));
        user.setUsername(rs.getString("username"));
        String decrypt = getDecrypt(rs);
        user.setPwdHash(decrypt);
        user.setBirthday(rs.getDate("birthday"));
        user.setSex(rs.getString("sex"));
        user.setUsertype(rs.getLong("usertype"));
        user.setIsfull(rs.getString("isfull"));
        user.setMoney(rs.getDouble("money"));
        return user;
    }

    private static String getDecrypt(ResultSet rs) throws SQLException {
        String pwdHash = rs.getString("pwdHash");
        String pwdHashCode = rs.getString("pwhHashCode");
        StrongTextEncryptor textEncryptor = new StrongTextEncryptor();
        textEncryptor.setPassword(pwdHash);
        String decrypt = textEncryptor.decrypt(pwdHashCode);
        return decrypt;
    }
}
